/**********************************************************************
*Created/Source by	:sfibich
*ScriptName			:utility.table.sqlScripts.sql
*Version			:1.0.120121
*Purpose			:creates a table to hold scripts.  Useful queries 
*					 may not always be useful as views or procedures
*					 for a number of reasons.  This table holds scripts
*					 that are useful to have around.
**********************************************************************/

BEGIN TRY
BEGIN TRANSACTION
	if exists (select 1 from sys.tables where name='sqlScripts' and
	schema_name(schema_id)='utility')
	BEGIN
		drop table utility.sqlScripts
		print 'SUCCESS! drop table utility.sqlScripts'
	END

	create table utility.sqlScripts (
	script_id smallInt not null
	,script_name varchar(256) not null
	,script varchar(max) not null
	,date_created datetime not null constraint df_sqlScripts_date_created default getdate() 
	,constraint pk_sqlScripts primary key (script_id)
	)

	merge metadata.utilityInfo as t
	using (
	select 
	'utility.sqlScripts' as info_desc
	,'1.0.120112' as value
	) as s
	on
	(s.info_desc=t.info_desc)
	when matched then
	update 
	set 
	t.value=s.value
	,t.date_time=getdate()
	when not matched then 
	insert
	(info_desc,value)
	values
	(s.info_desc,s.value);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
	PRINT error_message()
	ROLLBACK
END CATCH
